﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using MySQLStudio.DataAccess;
using MySQLStudio.Common;
using MySQLStudio.Common.Data;

namespace MySQLStudio.BusinessLogic
{
    public class TablesController
    {       
        public static List<Table> GetTablesInDatabase(string connectionString, string databaseName)
        {
            IDataReader reader = null;
            List<Table> lstTables = new List<Table>();

            try
            {
                string sqlQuery = InformationSchemaHelper.GetTablesInDatabaseCommand();
                Parameter prm = new Parameter();
                prm.ParameterName = "?databaseName";
                prm.DbType = DbType.String;
                prm.Value = databaseName;


                reader = MySqlDataAccess.ExecuteDataReader(connectionString, sqlQuery, prm);

                while (reader.Read())
                {
                    lstTables.Add(new Table(connectionString,
                        Convert.ToString(reader["TABLE_SCHEMA"]),
                        Convert.ToString(reader["TABLE_NAME"]),
                        Convert.ToString(reader["TABLE_TYPE"]),
                        Convert.ToString(reader["ENGINE"]),
                        DataUtils.GetNullableInt(reader, "TABLE_ROWS"),
                        DataUtils.GetNullableDateTime(reader, "CREATE_TIME"),
                        DataUtils.GetNullableDateTime(reader, "UPDATE_TIME"),
                        Convert.ToString(reader["TABLE_COLLATION"])));
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ErrorMessages.GetString(ErrorMessages.InformationSchema_Failed_to_extract_database_in_database, databaseName), ex);
            }
            finally
            {
                if (reader != null)
                {
                    if (!reader.IsClosed)
                        reader.Close();

                }
            }

            return lstTables;
        }

        public static ColumnCollection GetColumnsInTable(string connectionString, string databaseName, string tableName)
        {
            IDataReader reader = null;
            ColumnCollection columns = new ColumnCollection();

            try
            {
                string sqlQuery = InformationSchemaHelper.GetTableColumnsCommand();
                Parameter prmDatabase = new Parameter();
                Parameter prmTable = new Parameter();

                prmDatabase.ParameterName = "?databaseName";
                prmDatabase.DbType = DbType.String;
                prmDatabase.Value = databaseName;
                prmTable.ParameterName = "?tableName";
                prmTable.DbType = DbType.String;
                prmTable.Value = tableName;

                reader = MySqlDataAccess.ExecuteDataReader(connectionString, sqlQuery,
                    prmDatabase, prmTable);

                while (reader.Read())
                {
                    Column column = new Column();

                    string name = Convert.ToString(reader["COLUMN_NAME"]);
                    string extra = Convert.ToString(reader["EXTRA"]);
                    string dataType = Convert.ToString(reader["DATA_TYPE"]);
                    string columnDefault = Convert.ToString(reader["COLUMN_DEFAULT"]);
                    string isNullable = Convert.ToString(reader["IS_NULLABLE"]);
                    string columnKey = Convert.ToString(reader["COLUMN_KEY"]);
                    ulong? length = DBNull.Value.Equals(reader["CHARACTER_MAXIMUM_LENGTH"]) ? (ulong?)null : (ulong?)reader["CHARACTER_MAXIMUM_LENGTH"];
                    ulong? precision = DBNull.Value.Equals(reader["NUMERIC_PRECISION"]) ? (ulong?)null : (ulong?)reader["NUMERIC_PRECISION"];
                    ulong? scale = DBNull.Value.Equals(reader["NUMERIC_SCALE"]) ? (ulong?)null : (ulong?)reader["NUMERIC_SCALE"];
                    string comment = Convert.ToString(reader["COLUMN_COMMENT"]);
                    string columnType = Convert.ToString(reader["COLUMN_TYPE"]);

                    if (name != null)
                    {
                        column.Name = name;
                        column.IsIdentity = extra.IndexOf("auto_increment", StringComparison.InvariantCultureIgnoreCase) != -1;
                        column.DataType = dataType;
                        column.Default = columnDefault;
                        column.IsNullable = isNullable.Equals("YES", StringComparison.InvariantCultureIgnoreCase);
                        column.InPrimaryKey = columnKey.Equals("PRI", StringComparison.InvariantCultureIgnoreCase);
                        //column.InUniqueKey = columnKey.Equals("UNI", StringComparison.InvariantCultureIgnoreCase);
                        column.Length = length;
                        column.Precision = precision;
                        column.Scale = scale;
                        column.Unsigned = columnType.IndexOf("unsigned", StringComparison.InvariantCultureIgnoreCase) != -1;
                        column.Zerofill = columnType.IndexOf("zerofill", StringComparison.InvariantCultureIgnoreCase) != -1;
                        column.Comment = comment;
                        column.IsPersisted = true;
                        columns.Add(column);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ErrorMessages.GetString(ErrorMessages.InformationSchema_Failed_to_extract_columns_for_table, databaseName), ex);
            }
            finally
            {
                if (reader != null)
                {                    
                    reader.Close();
                }
            }

            return columns;
        }

        public static void Rename(string connectionString, string databaseName, string oldName, string newName)
        {
            try
            {
                string sqlQuery = string.Format(InformationSchemaHelper.GetRenameTableCommand(), databaseName, oldName, newName);                

                MySqlDataAccess.ExecuteNonQuery(connectionString, sqlQuery);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ErrorMessages.GetString(ErrorMessages.InformationSchema_Failed_to_rename_table, oldName), ex);
            }            
        }

        public static void RenameColumn(string connectionString, string databaseName, string tableName, Column column, string newName)
        {
            try
            {
                string columnQuery = TablesController.GenerateColumnQuery(newName, column.DataType, column.Length,
                    column.Precision, column.Scale, column.IsNullable, column.IsIdentity, column.Unsigned, column.Zerofill,
                    column.Collation, column.Comment);

                string sqlQuery = string.Format(InformationSchemaHelper.GetAlterTableColumnCommand(), databaseName, tableName,
                   "`'" + column.Name + "` " + columnQuery);

                MySqlDataAccess.ExecuteNonQuery(connectionString, sqlQuery);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ErrorMessages.GetString(ErrorMessages.InformationSchema_Failed_to_rename_table_column, column.Name, tableName), ex);
            }
        }

        public static void Delete(string connectionString, string databaseName, string tableName)
        {
            try
            {
                string sqlQuery = string.Format(InformationSchemaHelper.GetDropTableCommand(), databaseName, tableName);

                MySqlDataAccess.ExecuteNonQuery(connectionString, sqlQuery);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ErrorMessages.GetString(ErrorMessages.InformationSchema_Failed_to_delete_table, tableName), ex);
            }
        }

        public static void Create(string connectionString, string databaseName, Table table)
        {
            try
            {
                string sqlQuery = string.Format(InformationSchemaHelper.GetCreateTableCommand(), databaseName, table.Name,
                    TablesController.GenerateColumnCollectionQuery(table.Columns),
                    TablesController.GeneratePrimaryKeyQuery(table.Columns));

                MySqlDataAccess.ExecuteNonQuery(connectionString, sqlQuery);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ErrorMessages.GetString(ErrorMessages.InformationSchema_Failed_to_create_table, table.Name), ex);
            }
        }

        public static void Alter(string connectionString, string databaseName, Table table)
        {
            try
            {
                StringBuilder queryBuilder = new StringBuilder();                

                IEnumerable<Column> alteredColumns = table.Columns.Where(c => c.IsChanged);
                IEnumerable<Column> addedColumns = table.Columns.Where(c => !c.IsPersisted);

                if (alteredColumns.Count() > 0 || addedColumns.Count() > 0 || table.DeletedColumns.Count > 0)
                {
                    foreach (Column column in alteredColumns)
                    {
                        queryBuilder.AppendFormat(InformationSchemaHelper.GetAlterTableColumnCommand(),
                            databaseName, table.Name, "`" + column.BackupColumn.Name + "` " +
                            TablesController.GenerateColumnQuery(column));
                    }

                    foreach (Column column in addedColumns)
                    {
                        queryBuilder.AppendFormat(InformationSchemaHelper.GetAddTableColumnCommand(),
                            databaseName, table.Name, TablesController.GenerateColumnQuery(column));
                    }

                    foreach (Column column in table.DeletedColumns)
                    {
                        queryBuilder.AppendFormat(InformationSchemaHelper.GetDropTableColumnCommand(),
                            databaseName, table.Name, column.Name);
                    }

                    MySqlDataAccess.ExecuteNonQueryWithTransaction(connectionString, queryBuilder.ToString());
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ErrorMessages.GetString(ErrorMessages.InformationSchema_Failed_to_alter_table, table.Name), ex);
            }
        }

        private static string GenerateColumnQuery(Column column)
        {
            return TablesController.GenerateColumnQuery(column.Name, column.DataType, column.Length, column.Precision,
                column.Scale, column.IsNullable, column.IsIdentity, column.Unsigned, column.Zerofill, column.Collation,
                column.Comment);
        }

        private static string GenerateColumnQuery(string name, string dataType, ulong? length, ulong? precision, ulong? scale,
            bool isNullable, bool isIdentity, bool isUnsigned, bool isZerofill, string collation, string comment)
        {
            StringBuilder columnQueryBuilder = new StringBuilder();
            columnQueryBuilder.AppendFormat("`{0}` ", name);

            string strIsIdentity = string.Empty;
            string strIsUnsigned = string.Empty;
            string strIsZerofill = string.Empty;

            if (length != null)
            {
                columnQueryBuilder.AppendFormat("{0}({1}) ", dataType, length);
            }
            else if ((precision == null && scale == null && length == null)
                || (precision == 0 && scale == 0 && length == null))
            {
                columnQueryBuilder.AppendFormat("{0} ", dataType);
            }
            else if (precision != null && (scale == null || scale == 0))
            {
                columnQueryBuilder.AppendFormat("{0}({1}) ", dataType, precision);
            }
            else if (precision != null && scale != null && scale != 0)
            {
                columnQueryBuilder.AppendFormat("{0}({1},{2}) ", dataType, precision, scale);
            }            

            columnQueryBuilder.AppendFormat("{0} ", isNullable ? "NULL" : "NOT NULL");            

            if (isIdentity)
            {
                columnQueryBuilder.AppendFormat("{0} ", "AUTO_INCREMENT");
            }

            if (isUnsigned)
            {
                columnQueryBuilder.AppendFormat("{0} ", "UNSIGNED");
            }

            if (isZerofill)
            {
                columnQueryBuilder.AppendFormat("{0} ", "ZEROFILL");
            }

            if (!string.IsNullOrEmpty(collation))
            {
                columnQueryBuilder.AppendFormat("COLLATE {0} ", collation);
            }

            if (!string.IsNullOrEmpty(comment))
            {
                columnQueryBuilder.AppendFormat("COMMENT '{0}'", comment.Replace("'", "\\'"));
            }

            return columnQueryBuilder.ToString();
        }

        private static string GenerateColumnCollectionQuery(ColumnCollection columns)
        {
            StringBuilder columnCollectionQueryBuilder = new StringBuilder();

            if (columns != null)
            {
                for (int i = 0; i < columns.Count; i++)
                {
                    string columnQuery = TablesController.GenerateColumnQuery(columns[i].Name, columns[i].DataType,
                        columns[i].Length, columns[i].Precision, columns[i].Scale, columns[i].IsNullable,
                        columns[i].IsIdentity, columns[i].Unsigned, columns[i].Zerofill, columns[i].Collation,
                        columns[i].Comment);

                    Table table = columns.Container as Table;

                    if (table.IsPersisted)
                    {
                        if (i == 0)
                        {
                            columnCollectionQueryBuilder.AppendFormat("{0} FIRST ", columnQuery);
                        }
                        else
                        {
                            columnCollectionQueryBuilder.AppendFormat(", {0} AFTER `{1}`", columnQuery, columns[i - 1].Name);
                        }
                    }
                    else
                    {
                        if (i == 0)
                        {
                            columnCollectionQueryBuilder.AppendFormat("{0} ", columnQuery);
                        }
                        else
                        {
                            columnCollectionQueryBuilder.AppendFormat(", {0} ", columnQuery);
                        }
                    }
                }
            }

            return columnCollectionQueryBuilder.ToString();
        }

        private static string GeneratePrimaryKeyQuery(ColumnCollection columns)
        {
            StringBuilder primaryKeyQueryBuilder = new StringBuilder();            

            if (columns != null && columns.Count > 0)
            {
                for (int i = 0; i < columns.Count; i++)
                {
                    if (columns[i].InPrimaryKey)
                    {
                        if (primaryKeyQueryBuilder.Length == 0)
                        {
                            primaryKeyQueryBuilder.AppendFormat("`{0}` ", columns[i].Name);
                        }
                        else
                        {
                            primaryKeyQueryBuilder.AppendFormat(", `{0}`", columns[i].Name);
                        }
                    }
                }

                if (primaryKeyQueryBuilder.Length > 0)
                {
                    return string.Format(InformationSchemaHelper.GetCreatePrimaryKeyCommand(),
                        primaryKeyQueryBuilder);
                }
            }

            return primaryKeyQueryBuilder.ToString();
        }        
    }
}
